package com.example.ele_guarantee_hh_pro.comclass;
import com.cloudcc.deveditor.core.CCObject;
import com.cloudcc.deveditor.core.CCService;
import com.cloudcc.deveditor.core.ServiceResult;
import com.cloudcc.deveditor.core.UserInfo;

import net.sf.json.JSONObject;
import net.sf.json.JSONArray;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/*导入客户后台类*/
public class ImportAccount {


    private CCService cs;
    private UserInfo userInfo;
    private static int SIZE=80;

    //管户状态
    private String ghzt_continue_failure = "已失效";
    //管户状态
    private String ghzt_continue = "未生效";
    //正式环境客户记录类型
    //private String jllx = "202385551266A597FF1B";

    //测试环境客户记录类型
    private String jllx = "20211C59581DC5CZOn5N";
    //状态待分配
    private String account_dfp = "待分配";
    //
    private String account_dgj = "待跟进";

    //客户开函状态
    private String deal_status = "未开函";

    public  ImportAccount(UserInfo userInfo) {
        this.userInfo = userInfo;
        cs = new CCService(userInfo);
    }

    //获得要导入客户的数据
    public String import_data(String data) throws Exception {
        ArrayList<CCObject> ccObjects = new ArrayList<>();
        String uuid = UUID.randomUUID().toString();
        int success_num=0;
        //将data转为JsonArray数组的形式
        JSONArray jsonArray = JSONArray.fromObject(data);
        if (jsonArray.size() > 0) {
            for (int i = 0; i < jsonArray.size(); i++) {
                //根据i的值获取第i项
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                //公共资源交易中心
                String fk_psmc = jsonObject.getString("fk_psmc")==null?"":jsonObject.getString("fk_psmc")+"";
                //统一社会信用代码
                String invoiceTaxNo = jsonObject.getString("invoiceTaxNo")==null?"":jsonObject.getString("invoiceTaxNo")+"";
                //查询线下保函
                JSONObject check_json = new JSONObject();
                String uid = UUID.randomUUID().toString();
                JSONObject check_data = new JSONObject();
                check_data.put("cusCertNo",invoiceTaxNo);
                String innerPlatformCode=get_psmc(fk_psmc);
                check_data.put("innerPlatformCode",innerPlatformCode);
                check_json.put("invokeId",uid);
                check_json.put("data",check_data);
                Boolean offlineeleopp = false;
                try {
                    offlineeleopp = offlineeleopp(check_data.toString());
                } catch (Exception e) {
                    e.printStackTrace();
                }
                //客户名称
                String name = jsonObject.getString("name")==null?"":jsonObject.getString("name")+"";
                //社会统一信用代码
                //省份
                String province = jsonObject.getString("province")==null?"":jsonObject.getString("province")+"";
                //城市
                String fk_city_name = jsonObject.getString("fk_city_name")==null?"":jsonObject.getString("fk_city_name")+"";
                //区县
                String fk_country_name = jsonObject.getString("fk_country_name")==null?"":jsonObject.getString("fk_country_name")+"";
                //注册地址
                String registeredAddress = jsonObject.getString("registeredAddress")==null?"":jsonObject.getString("registeredAddress")+"";
                //法定代表人
                String full_name = jsonObject.getString("full_name")==null?"":jsonObject.getString("full_name")+"";
                //注册资本
                String registered_capital = jsonObject.getString("registered_capital")==null?"":jsonObject.getString("registered_capital")+"";
                //企业性质
                String qyxz = jsonObject.getString("qyxz")==null?"":jsonObject.getString("qyxz")+"";
                //成立日期
                String establishDate = jsonObject.getString("establishDate")==null?"":jsonObject.getString("establishDate")+"";
                //所有人（客户经理->员工编号）
                String ownerid = jsonObject.getString("ownerid")==null?"":jsonObject.getString("ownerid")+"";
                String first_con_num = jsonObject.getString("first_con_num")==null?"":jsonObject.getString("first_con_num")+"";
                String second_con_num = jsonObject.getString("second_con_num")==null?"":jsonObject.getString("second_con_num")+"";
                String gczzsl = jsonObject.getString("gczzsl")==null?"":jsonObject.getString("gczzsl")+"";
                String main_type = jsonObject.getString("main_type")==null?"":jsonObject.getString("main_type")+"";
                String main_area = jsonObject.getString("main_area")==null?"":jsonObject.getString("main_area")+"";
                String ssqc = jsonObject.getString("ssqc")==null?"":jsonObject.getString("ssqc")+"";
                String frequece = jsonObject.getString("frequece")==null?"":jsonObject.getString("frequece")+"";
                if(offlineeleopp){
                    CCObject account = new CCObject("Account");
                    String city_id = "";
                    String country_id = "";
                    boolean is_have = find_account(invoiceTaxNo);
                    //查询城市的id
                    String city_sql = "select id from city "+
                            "where province='"+province+"' "+
                            "and name='"+fk_city_name+"' "+
                            "and is_deleted='0' ";
                    List<CCObject> city_list = cs.cqlQuery("city", city_sql);
                    if (city_list.size()>0){
                        city_id = city_list.get(0).get("id") == null ? "" : city_list.get(0).get("id") + "";
                    }
                    //查询区县的id
                    String country_sql = "select id from country "+
                            "where city='"+city_id+"' " +
                            "and name='"+fk_country_name+"' " +
                            "and is_deleted='0'";
                    List<CCObject> country_list = cs.cqlQuery("country", country_sql);
                    if (country_list.size()>0){
                        country_id = country_list.get(0).get("id")==null?"":country_list.get(0).get("id")+"";
                    }
                    account.put("name", name);
                    account.put("invoiceTaxNo", invoiceTaxNo);
                    account.put("province", province);
                    account.put("fk_city_name", city_id);
                    account.put("fk_country_name", country_id);
                    account.put("registeredAddress", registeredAddress);
                    account.put("full_name", full_name);
                    account.put("registered_capital", registered_capital);
                    account.put("qyxz", qyxz);
                    account.put("establishDate", establishDate);
                    //开函状态
                    account.put("deal_status",deal_status);
                    account.put("recordtype", jllx);
                    if(is_have){
                        if(ownerid.equals("")){
                            insert_rs(uuid,fk_city_name,second_con_num,fk_country_name,qyxz,province,
                                    registered_capital,first_con_num,ownerid,registeredAddress,ssqc,invoiceTaxNo,
                                    establishDate,frequece,full_name,fk_psmc,name,main_type,
                                    main_area,gczzsl,"该客户已存在，请添加管户经理！");

                        }else{
                            account.put("follow_status", account_dgj);
                            ownerid=get_account_khjl(ownerid);
                            boolean have_role = is_have_role(invoiceTaxNo, innerPlatformCode);
                            if(have_role){
                                //不行
                                insert_rs(uuid,fk_city_name,second_con_num,fk_country_name,qyxz,province,
                                        registered_capital,first_con_num,ownerid,registeredAddress,ssqc,invoiceTaxNo,
                                        establishDate,frequece,full_name,fk_psmc,name,main_type,
                                        main_area,gczzsl,"已有管户权！");

                            }else{
                                account.put("ownerid", ownerid);
                                ServiceResult insert = cs.insert(account);
                                String fk_account=insert.get("id")+"";
                                //插入管户小组
                                insert_manage_group("是",fk_account,
                                        innerPlatformCode,ownerid,ghzt_continue,
                                        invoiceTaxNo,province,registeredAddress,city_id,country_id);
                                //插入公招信息
                                insert_ele_open_info(fk_account,first_con_num,
                                        second_con_num,gczzsl,main_type,main_area,ssqc);
                                //插入投标频率
                                insert_ele_num(frequece,fk_account,innerPlatformCode);
                                //成功
                                success_num++;
                            }
                        }
                    }else{
                        if(ownerid.equals("")){
                            account.put("follow_status", account_dfp);
                            ownerid = get_pool_id();
                            account.put("ownerid", ownerid);
                            ServiceResult insert = cs.insert(account);
                            String fk_account=insert.get("id")+"";
                            //插入公招信息
                            insert_ele_open_info(fk_account,first_con_num,
                                    second_con_num,gczzsl,main_type,main_area,ssqc);
                            //插入投标频率
                            insert_ele_num(frequece,fk_account,innerPlatformCode);
                            //成功
                            success_num++;
                        }else{
                            account.put("follow_status", account_dgj);
                            ownerid=get_account_khjl(ownerid);
                            account.put("ownerid", ownerid);
                            ServiceResult insert = cs.insert(account);
                            String fk_account=insert.get("id")+"";
                            //管户小组
                            insert_manage_group("否",fk_account,
                                    innerPlatformCode,ownerid,ghzt_continue,
                                    invoiceTaxNo,province,registeredAddress,city_id,country_id);
                            //插入公招信息
                            insert_ele_open_info(fk_account,first_con_num,
                                    second_con_num,gczzsl,main_type,main_area,ssqc);
                            //插入投标频率
                            insert_ele_num(frequece,fk_account,innerPlatformCode);
                            //成功
                            success_num++;
                        }
                    }
                }else{
                    insert_rs(uuid,fk_city_name,second_con_num,fk_country_name,qyxz,province,
                            registered_capital,first_con_num,ownerid,registeredAddress,ssqc,invoiceTaxNo,
                            establishDate,frequece,full_name,fk_psmc,name,main_type,
                            main_area,gczzsl,"线下保函系统已存在该客户！");
                }
            }
            insert_success(uuid,success_num);
        }

        JSONObject rs = new JSONObject();
        rs.put("code","200");
        rs.put("msg","数据正在导入，请稍后到结果页面进行查看导入情况");
        return rs.toString();
    }

    //插入公招信息
    public void insert_ele_open_info (String fk_account,
                                      String first_con_num,
                                      String second_con_num,
                                      String gczzsl,
                                      String main_type,
                                      String main_area,
                                      String ssqc) throws Exception{
        CCObject ele_open_info = new CCObject("ele_open_info");
        ele_open_info.put("first_con_num", first_con_num);
        ele_open_info.put("fk_account", fk_account);
        ele_open_info.put("second_con_num", second_con_num);
        ele_open_info.put("gczzsl", gczzsl);
        ele_open_info.put("main_type", main_type);
        ele_open_info.put("main_area", main_area);
        ele_open_info.put("ssqc", ssqc);
        cs.insert(ele_open_info);
    }

    //更新公招信息
    public void update_ele_open_info(String id,String fk_account,
                                     String first_con_num,
                                     String second_con_num,
                                     String gczzsl,
                                     String main_type,
                                     String main_area,
                                     String ssqc) throws Exception{
        CCObject ele_open_info = new CCObject("ele_open_info");
        ele_open_info.put("id", id);
        ele_open_info.put("first_con_num", first_con_num);
        ele_open_info.put("fk_account", fk_account);
        ele_open_info.put("second_con_num", second_con_num);
        ele_open_info.put("gczzsl", gczzsl);
        ele_open_info.put("main_type", main_type);
        ele_open_info.put("main_area", main_area);
        ele_open_info.put("ssqc", ssqc);
        cs.update(ele_open_info);
    }


    //插入投标频率的数据
    public void insert_ele_num (String frequece,
                                String fk_account,
                                String fk_psmc) throws Exception{
        CCObject ele_num = new CCObject("ele_num");
        ele_num.put("fk_psmc", fk_psmc);
        ele_num.put("frequece", frequece);
        ele_num.put("fk_account", fk_account);
        cs.insert(ele_num);
    }

    //更新投标频率的数据
    public void update_ele_num (String id,String frequece,
                                String fk_account,
                                String fk_psmc) throws Exception{
        CCObject ele_num = new CCObject("ele_num");
        ele_num.put("id", id);
        ele_num.put("fk_psmc", fk_psmc);
        ele_num.put("frequece", frequece);
        ele_num.put("fk_account", fk_account);
        cs.update(ele_num);
    }


    //获取公共交易资源中心的id
    public String get_psmc(String uncode) throws Exception{
        String id="";
        String psmc_sql = "select id from psmc " +
                "where uncode ='" + uncode + "'" +
                "and is_deleted='0'";
        List<CCObject> psmc_list = cs.cqlQuery("psmc", psmc_sql);
        if (psmc_list.size() > 0) {
            id = psmc_list.get(0).get("id") == null ? "" : psmc_list.get(0).get("id") + "";
        }
        return id;
    }

    //查询验证客户是否存在
    public boolean find_account(String code) throws Exception{
        //统一社会信用代码查询客户
        String account_sql = "select * from Account " +
                "where InvoiceTaxNo ='" + code + "'" +
                "and is_deleted ='0'";
        List<CCObject> account = cs.cqlQuery("Account", account_sql);
        if (account.size() > 0) {
            return true;
        }
        return false;
    }

    //获取所有人（客户经理）的用户id信息
    public String get_account_khjl(String employeenum) throws Exception{
        List<CCObject> ccuser = cs.cquery("ccuser", "employeenum='" + employeenum + "'");
        String userid="";
        if(ccuser.size()>0){
            userid=ccuser.get(0).get("id")==null?"":ccuser.get(0).get("id")+"";
        }
        return userid;
    }

    //查询这个操作人的用户信息，获取到：电子保函绑定公海池id
    public String get_pool_id() throws Exception{
        //当前操作人
        String userId = userInfo.getUserId();
        String pool = "";
        String ccuser_sql = "select * from ccuser " +
                "where id ='" + userId + "'" +
                "and is_deleted ='0'";
        List<CCObject> ccuser = cs.cqlQuery("ccuser", ccuser_sql);
        if (ccuser.size() > 0) {
            pool = ccuser.get(0).get("pool") == null ? "" : ccuser.get(0).get("pool") + "";//电子保函绑定公海池
        }
        return pool;
    }
    //是否已经有管护权
    public boolean is_have_role(String account_code,String fk_pmsc) throws Exception{
        List<CCObject> manage_group = cs.cquery("manage_group", "name='" + account_code + "' and fk_psmc='" + fk_pmsc + "' and ghzt!='"+ghzt_continue_failure+"'");
        if(manage_group.size()>0){
            return true;
        }
        return false;
    }

    //是否存在需要拾取的管护权
    public boolean is_have_roletoget(String account_code,String userid) throws Exception{
        List<CCObject> manage_group = cs.cquery("manage_group", "name='" + account_code + "' and fk_ccuser='" + userid + "' and ghzt='"+ghzt_continue_failure+"'");
        if(manage_group.size()>0){
            return true;
        }
        return false;
    }

    //插入管户小组
    public void insert_manage_group(String is_shared,
                                    String fk_ccuser,
                                    String fk_psmc,
                                    String fk_account,
                                    String ghzt_continue,
                                    String code,
                                    String province1,
                                    String lxdz,
                                    String fk_city_name1,
                                    String fk_country_name1) throws Exception{

        CCObject manage_group = new CCObject("manage_group");
        manage_group.put("name", code);
        manage_group.put("fk_ccuser", fk_ccuser);
        manage_group.put("fk_account", fk_account);
        manage_group.put("fk_psmc", fk_psmc);
        manage_group.put("province1", province1);
        manage_group.put("fk_city_name1", fk_city_name1);
        manage_group.put("fk_country_name1", fk_country_name1);
        manage_group.put("lxdz", lxdz);
        manage_group.put("is_shared", is_shared);
        manage_group.put("ghzt", ghzt_continue);
        String gen = gen(lxdz);
        String[] split = gen.split(",");
        manage_group.put("dlwzco", split[0]);
        manage_group.put("dlwzla", split[1]);
        cs.insert(manage_group);
    }



    //插入结果数据
    public void insert_rs(String name,String city,
                          String second_num,String country,
                          String qyzx,String province,
                          String zczb,String first_num,
                          String khjl,String zcdz,
                          String ssqc,String code,
                          String clrq,String tbpc,
                          String fddbr,String psmc_num,
                          String khmc,String main_type,
                          String main_area,String gczzsl,
                          String lose_reason) throws Exception{
        CCObject impdata = new CCObject("impdata");
        impdata.put("name",name);
        impdata.put("city",city);
        impdata.put("second_num",second_num);
        impdata.put("country",country);
        impdata.put("qyzx",qyzx);
        impdata.put("province",province);
        impdata.put("zczb",zczb);
        impdata.put("first_num",first_num);
        impdata.put("khjl",khjl);
        impdata.put("zcdz",zcdz);
        impdata.put("ssqc",ssqc);
        impdata.put("code",code);
        impdata.put("clrq",clrq);
        impdata.put("tbpc",tbpc);
        impdata.put("fddbr",fddbr);
        impdata.put("psmc_num",psmc_num);
        impdata.put("khmc",khmc);
        impdata.put("main_type",main_type);
        impdata.put("main_area",main_area);
        impdata.put("gczzsl",gczzsl);
        impdata.put("lose_reason",lose_reason);
        cs.insert(impdata);
    }

    public void insert_success(String uuid,int num) throws Exception{
        CCObject impsuccess = new CCObject("impsuccess");
        impsuccess.put("name",uuid);
        impsuccess.put("num",num);
        cs.insert(impsuccess);
    }

    //查询导入的数据
    public String query_import_lose_reason() throws Exception{
        String userId = userInfo.getUserId();
        String uuid="";
        String get_uuid_sql="select name from impdata where ownerid='"+userId+"'" +
                " and is_deleted='0' " +
                "order by createdate desc" +
                "limit 1";
        List<CCObject> uuid_list = cs.cqlQuery("impdata", get_uuid_sql);
        if(uuid_list.size()>0){
            uuid=uuid_list.get(0).get("name")==null?"":uuid_list.get(0).get("name")+"";
        }
        String impdata_sql = "select * from impdata " +
                "where name='"+uuid+"'" +
                "and is_deleted='0'";
        List<CCObject> impdata_list = cs.cqlQuery("impdata", impdata_sql);
        if (impdata_list.size()>0){
            return impdata_list.toString();
        }
        return null;
    }
    //查询导入的成功条数
    public String query_success_num() throws Exception{
        String userId = userInfo.getUserId();
        String num="0";
        String uuid="";
        String get_uuid_sql="select name from impdata where ownerid='"+userId+"'" +
                " and is_deleted='0' " +
                "order by createdate desc" +
                "limit 1";
        List<CCObject> uuid_list = cs.cqlQuery("impdata", get_uuid_sql);
        if(uuid_list.size()>0){
            uuid=uuid_list.get(0).get("name")==null?"":uuid_list.get(0).get("name")+"";
        }
        List<CCObject> impsuccess = cs.cquery("impsuccess", "name='" + uuid + "'");
        if(impsuccess.size()>0){
            num=impsuccess.get(0).get("num")==null?"0":impsuccess.get(0).get("num")+"";
        }
        return num;
    }


    /*查询线下电子保函的绑定关系*/
    public Boolean offlineeleopp(String data) throws Exception {
        String YES="502";
        String NO="200";
        String Error="400";
        SaveAccountTest saveAccountTest = new SaveAccountTest(userInfo);
        String s = saveAccountTest.postQuerystatus(data);
        JSONObject jsonObject = JSONObject.fromObject(s);
        String code = jsonObject.getString("code");
        if(code.equals(YES)||code.equals(Error)){
            return false;
        }else if(code.equals(NO)){
            return true;
        }
        return false;
    }
    /*查询线下电子保函的绑定关系返回信息*/
    public String offlineeleopp_msg(String data) throws Exception {
        String msg="";
        String NO="200";
        SaveAccountTest saveAccountTest = new SaveAccountTest(userInfo);
        String s = saveAccountTest.postQuerystatus(data);
        JSONObject jsonObject = JSONObject.fromObject(s);
        String code = jsonObject.getString("code");
        if(!code.equals(NO)){
            msg=jsonObject.getString("msg");
        }
        return msg;
    }
    /*调用高德地图通过经纬度查询*/
    public String regeo(String location) {
        URLConnection con = null;
        BufferedReader buffer = null;
        StringBuffer resultBuffer = null;
        String KEY = "7350620efd969ea50d71be2cf9b0467c";
        String regeo = "https://restapi.amap.com/v3/geocode/regeo?key=" + KEY;
        regeo += "&location=" + location;
        try {
            URL url = new URL(regeo);
            con = url.openConnection();
            con.setRequestProperty("Content-Type", "application/json;charset=GBK");
            //允许写出
            con.setDoOutput(true);
            //允许读入
            con.setDoInput(true);
            //不使用缓存
            con.setUseCaches(false);
            //得到响应流
            InputStream inputStream = con.getInputStream();
            //将响应流转换成字符串
            resultBuffer = new StringBuffer();
            String line;
            buffer = new BufferedReader(new InputStreamReader(inputStream, "UTF-8"));
            while ((line = buffer.readLine()) != null) {
                resultBuffer.append(line);
            }
            return String.valueOf(resultBuffer);

        } catch (Exception e) {
            return String.valueOf(e);
        }
    }

    //调用高德地图通过地理位置查询经纬度
    public String gen(String address) throws Exception{
        String location="";
        URLConnection con = null;
        BufferedReader buffer = null;
        StringBuffer resultBuffer = null;
        String KEY = "7350620efd969ea50d71be2cf9b0467c";
        String regeo = "https://restapi.amap.com/v3/geocode/geo?key=" + KEY;
        regeo += "&address=" + address;
        try {
            URL url = new URL(regeo);
            con = url.openConnection();
            con.setRequestProperty("Content-Type", "application/json;charset=GBK");
            //允许写出
            con.setDoOutput(true);
            //允许读入
            con.setDoInput(true);
            //不使用缓存
            con.setUseCaches(false);
            //得到响应流
            InputStream inputStream = con.getInputStream();
            //将响应流转换成字符串
            resultBuffer = new StringBuffer();
            String line;
            buffer = new BufferedReader(new InputStreamReader(inputStream, "UTF-8"));
            while ((line = buffer.readLine()) != null) {
                resultBuffer.append(line);
            }
            JSONObject result = JSONObject.fromObject(resultBuffer.toString());
            JSONArray geocodes = result.getJSONArray("geocodes");
            location= geocodes.getJSONObject(0).getString("location");

        } catch (Exception e) {
            return String.valueOf(e);
        }
        return location;
    }

}
